﻿using System;
using System.Collections.Generic;
using System.Diagnostics;
using OfficeOpenXml;
using OfficeOpenXml.DataValidation;

namespace ShieldTunnel2
{
	public static class ExcelHelper
	{
		#region Excel Helpers

		/// <summary>
		/// 为指定单元格添加下拉选项
		/// </summary>
		/// <param name="sheet"></param>
		/// <param name="rowIndex">行索引(1 based)</param>
		/// <param name="colIndex">列索引(1 based)</param>
		/// <param name="options">选项列表</param>
		public static void AddValidationListToCell(ExcelWorksheet sheet,
			int rowIndex, int colIndex, IList<string> options)
		{
			Debug.Assert((rowIndex >= 1 && colIndex >= 1) && IsListNotEmpty(options));

			var cellAddress = CellIndexC1R1ToA1(rowIndex, colIndex);
			AddValidationListToRange(sheet, cellAddress, options);
		}

		/// <summary>
		/// 为指定range添加下拉选项
		/// </summary>
		/// <param name="sheet"></param>
		/// <param name="rowFrom"></param>
		/// <param name="colFrom"></param>
		/// <param name="rowTo"></param>
		/// <param name="colTo"></param>
		/// <param name="options">选项列表</param>
		public static void AddValidationListToRange(ExcelWorksheet sheet,
			int rowFrom, int colFrom, int rowTo, int colTo,
			IList<string> options)
		{
			Debug.Assert((1 <= rowFrom && rowFrom <= rowTo)
						 && (1 <= colFrom && colFrom <= colTo)
						 && IsListNotEmpty(options));

			var rangeAddress = RangeIndex123ToABC(rowFrom, colFrom, rowTo, colTo);
			AddValidationListToRange(sheet, rangeAddress, options);
		}

		/// <summary>
		/// 为指定range添加下拉选项
		/// </summary>
		/// <param name="sheet"></param>
		/// <param name="rangeAddress">A1格式的range字符串</param>
		/// <param name="options">选项列表</param>
		public static void AddValidationListToRange(ExcelWorksheet sheet,
			string rangeAddress, IList<string> options)
		{
			Debug.Assert(!string.IsNullOrWhiteSpace(rangeAddress) && IsListNotEmpty(options));

			var validation = sheet.DataValidations.AddListValidation(rangeAddress);
			validation.ShowErrorMessage = true;
			validation.Prompt = "从下拉列表中选择";
			validation.ErrorStyle = ExcelDataValidationWarningStyle.stop;
			validation.ErrorTitle = "输入的数据无效";
			validation.Error = "请从下拉列表中选择一项";
			foreach (var option in options)
			{
				validation.Formula.Values.Add(option);
			}
		}

		/// <summary>
		/// 将Excel单元格C1R1样式的索引模式转换为对于的A1样式的索引模式
		/// </summary>
		/// <param name="rowIndex">单元格行索引数(1 based)</param>
		/// <param name="colIndex">单元格列索引数(1 based)</param>
		/// <returns>单元格A1样式的索引</returns>
		public static string CellIndexC1R1ToA1(int rowIndex, int colIndex)
		{
			Debug.Assert(rowIndex > 0 && colIndex > 0);
			return $"{ColumnIndex123ToABC(colIndex)}{rowIndex}";
		}

		/// <summary>
		/// 将Excel单元格AB样式的列名转换为列数索引(0 based)
		/// </summary>
		/// <param name="colStr">列索引号</param>
		/// <returns>列数索引(1 based)</returns>
		public static int ColumnIndexABCTo123(string colStr)
		{
			var colIndex = 0;
			const int baseValue = 'Z' - 'A' + 1;
			for (var i = 0; i < colStr.Length; i++)
			{
				var c = colStr[colStr.Length - 1 - i];
				Debug.Assert('A' <= c && c <= 'Z');
				colIndex += (c - 'A' + 1) * (int)Math.Pow(baseValue, i);
			}
			return colIndex;
		}

		/// <summary>
		/// 将123形式的列索引转换为ABC形式的列索引
		/// </summary>
		/// <param name="colIndex">列索引号(1 based)</param>
		/// <returns></returns>
		public static string ColumnIndex123ToABC(int colIndex)
		{
			Debug.Assert(colIndex > 0);
			const int baseValue = 'Z' - 'A' + 1;
			var numList = new List<int>();
			var quo = colIndex;
			do
			{
				var rem = (quo - 1) % baseValue + 1;
				quo = (int)((quo - 1) / baseValue);
				numList.Add(rem);
			} while (quo > 0);

			var colStr = string.Empty;
			foreach (var num in numList)
			{
				colStr = (char)('A' + num - 1) + colStr;
			}
			return colStr;
		}

		/// <summary>
		/// 将123形式的range索引转换为ABC形式的range索引
		/// </summary>
		/// <param name="rowFrom"></param>
		/// <param name="colFrom"></param>
		/// <param name="rowTo"></param>
		/// <param name="colTo"></param>
		/// <returns></returns>
		public static string RangeIndex123ToABC(int rowFrom, int colFrom, int rowTo, int colTo)
		{
			Debug.Assert((1 <= rowFrom && rowFrom <= rowTo)
						 && (1 <= colFrom && colFrom <= colTo));
			return $"{CellIndexC1R1ToA1(rowFrom, colFrom)}:{CellIndexC1R1ToA1(rowTo, colTo)}";
		}

		#endregion

		public static bool IsEmptyCell(ExcelRange cell) => string.IsNullOrWhiteSpace(cell.Value?.ToString());

		public static bool IsMergedCell(ExcelRange cell) => cell.Merge;

		public static bool IsMergedContentCell(ExcelWorksheet s, int r, int c)
		{
			var cell = s.Cells[r, c];
			return IsMergedCell(cell) && (!IsEmptyCell(cell));
		}

		public static bool IsMergedEmptyCell(ExcelWorksheet s, int r, int c)
		{
			var cell = s.Cells[r, c];
			return IsMergedCell(cell) && IsEmptyCell(cell);
		}

		public static bool IsNomergedEmptyCell(ExcelWorksheet s, int r, int c)
		{
			var cell = s.Cells[r, c];
			return (!IsMergedCell(cell)) && IsEmptyCell(cell);
		}

		public static string ParseCellStringValue(ExcelWorksheet sheet, int row, int col)
		{
			var cell = sheet.Cells[row, col];
			Debug.Assert(!IsEmptyCell(cell));
			return cell.Value.ToString();
		}

		public static int ParseCellIntValue(ExcelWorksheet sheet, int row, int col)
			=> int.Parse(ParseCellStringValue(sheet, row, col));

		#region Utility Functions

		public static bool IsListNotEmpty<TItem>(IList<TItem> list)
			=> list != null && list.Count > 0;

		#endregion
	}
}
